Looping Macros This short article outlines several practical uses for macros which loop, or repeat tasks, until the user chooses to stop performing an operation. Simplifying Use of Numeric Keypad This macro allows you to use the numeric keypad at the right side of the keyboard. Of course, you can always use the keypad by pressing the [Num Lock] key, or by holding down the [Shift] key while typing the numbers on the pad. Unfortunately, the cursor motion keys are on the same pad, and you usually move the cell pointer after typing each number. This macro takes care of moving the cell pointer, so it is named \M, for Move. While using it, you should leave the [Num Lock] on. The NUM indicator in the lower right-hand corner of the screen is displayed when the numeric keypad is set for the input of numbers only. The Move Macro takes advantage of the special macro instruction {?}. This stops execution of the macro and gives control of 1-2-3 back to you. The macro resumes when you press [Enter]. 1-2-3 lets you know that you are still in a macro by displaying the CMD indicator next to the Mode indicator in the upper right-hand corner of the screen. The Move Macro waits until you make an entry, and moves the cell pointer when you press [Enter]. For this example the macro will move the cell pointer down, but it could be written to move it laterally or diagonally. Without looping, it consists of just two instructions: Range Name Command Comments \M {?} Wait until [Enter]. {down} Move the cell pointer down. When you press Alt-M, the CMD indicator is displayed, but nothing else happens because the first instruction is to wait. Type an entry, press [Enter], and the cell pointer will move down. Since you don't need a cursor key, you could leave the [Num Lock] on. So far, however, this is not very helpful since typing Alt-M before each entry is more bother than any of the alternatives. Creating a Looping Macro The solution is the loop. To make the macro repeat itself, an "invisible" X command in the 1-2-3 top level command menu (the one that begins "Worksheet...Range...") must be used. /X commands may only be used in macros. 1-2-3 commands are written in macros as a slash (/) followed by one or more letters. The /X commands consist of /X followed by one letter. For example, /XQ quits a macro and /XM puts up the command menu. /XG is the macro Goto command that may be used to make a macro loop. (By the way, don't confuse /XG with the F5 {goto} key, which moves the cell pointer, and which can also be used in macros.) The /XG command tells 1-2-3 to get its next macro instruction from somewhere other than the rest of the label or the next label cell down, as it normally would. After the G, enter a cell coordinate or range name, to tell 1-2-3 where to get its next set of instructions. Enter a tilde (~) after the coordinate or range name. HINT: Using range names is better than using cell coordinates because it will insure that the macro will function correctly when you /Move cells, or /Worksheet Insert and Delete Rows and Columns. Assign range names to individual cells containing macro commands. To keep things clear, place the range names as labels in the column to the left of the macro instructions, and use the /Range Name Label Right command to assign them to the first step of each macro routine. If a named range contains more than one cell, the /XG command will begin execution at the top left corner of the range. In this case, it would be preferable for the entire macro to repeat, and it already has a range name, \M. To make the macro repeat, just type this label in the cell below the cell containing {down}: /XG\M~ Go back to the beginning of the macro. After the macro has moved the cell pointer down, it performs the instruction in the next cell in the macro. This instruction tells 1-2-3 to go back to the start of the macro, where it again waits until you press [Enter], moves the cell pointer down, repeating indefinitely. Stopping This Macro Now that an endless loop has been established, how can it be stopped? The best method is to hold down the [Ctrl] key and press [Scroll-Lock/Break]. When you see ERROR in the Mode Indicator, don't worry. Press [Enter], [Esc], or [Ctrl-Break] again and everything will be fine. This method can be used to stop execution of any macro, a printing operation, or a long /Data Table calculation. Before you [Ctrl-Break] out of this macro, however, consider entering another column of numbers. While 1-2-3 is waiting (the CMD indicator is on), you may move the cell pointer. Just turn off the [Num-Lock] or press shift while pressing the cursor keys. Otherwise, you'll get a string of digits typed in the current cell. Clarifying the Meaning of Tilde (~) You may have some lingering questions about the when's and why's of the tilde. To further illustrate its use, examine the following macro. It moves down a column of labels, editing each one and stopping to allow you to add to the existing label. \E {edit}{?}~ Edit the label and wait for [Enter]. {down} Move the cell pointer down. /XG\E~ Repeat the macro from the beginning. There are only two differences between this macro and the Move Macro. The {edit} key is obvious. But why the tilde after the {?}; you have to press [Enter] to resume the macro anyway. The answer is that the {?} "swallows" the [Enter]; it is never sent to 1-2-3. To exit Edit mode, you must press [Enter]. This is done by the macro when it reads the tilde. In the Move Macro, no [Enter] was needed and, just as none is needed when you are entering values or labels, no tilde is required after the {?}. {?} as a Breakpoint If you're setting out to do more extensive macro programming, one more hint is in order. The {?} wait instruction is what programmers call a "breakpoint." It stops everything and lets you examine your work. If you've written a large macro and it's not doing what you think it should, put breakpoints at the ends of several of your macro labels. Then, when execution stops, you can look around the spreadsheet to see if, so far, things are as they should be. In Summary The pointer movement was entered into three cells in a column, but could be stored in any number of ways. If you use it a lot, you might want to put it in one line: \M {?}{down}/XG\M~ Wait, move down and repeat. The Move Macro is handy; it could be tremendously helpful when entering a large amount of numeric data.